package com.navi.loaddata.loadService.impl.baseData;


import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.base.MDefectGpRlnD;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MesRepositoryImpl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.loadService.impl.edcdata.SqlUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.amqp.core.AmqpTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


/**
 * @author fuchenyang
 * @date 2018/4/19
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
        rollbackFor = {RuntimeException.class, Exception.class})

public class LoadDefectGPRlnDataService {
    private static final Logger logger = LoggerFactory.getLogger(LoadDefectGPRlnDataService.class);

    @Autowired
    private MesRepositoryImpl mesDao;

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private SqlUtils sqlUtils;

    @Autowired
    private AmqpTemplate rabbitTemplate;

    private String shopid;
    private String opeid;
    private String defectcode;

    public void loadDefectGPRlnDataMain(EtlConfD etlConfD) throws ETLException {
        Session session = gpDao.getEntityManagerSession();
        try {
            logger.info("loadDefectGPRlnData Start");
            loadDefectGPRlnData(session, etlConfD);
            logger.info("loadDefectGPRlnData finish");
        } catch (ETLException e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException(LoggerUtil.stackTraceToString(e));
        }
    }

    private void loadDefectGPRlnData(Session session, EtlConfD etlConfD) throws ETLException {
        //获取dm.m_defect_gp_rln_d表新插入的defect_code行（defect_code_seq值为null）
        QueryRunner qr_gp = JdbcUtil.getQueryRunner_gp();

        String sql = "select shop_id,ope_id,defect_code from dm.m_defect_gp_rln_d" +
                " where ope_id <> 'Z_ALL'" +
                " and defect_code_seq is null" +
//                " and defect_code not like '*%'" +
                " and shop_id = '" + etlConfD.getShop() + "'" +
                " order by shop_id,ope_id,defect_code";


        try {
            List<MDefectGpRlnD> defectOpeList = qr_gp.query(sql, new BeanListHandler<MDefectGpRlnD>(MDefectGpRlnD.class));  //获取defect_code_seq值为null的shop_id,ope_id

            for(int i = 0; i < defectOpeList.size(); i++) {
                shopid = defectOpeList.get(i).getShop_Id();
                opeid = defectOpeList.get(i).getOpe_Id();
                defectcode = defectOpeList.get(i).getDefect_Code();

                //获取dm.m_defect_gp_rln_d表的单工厂各站点的defect数量
                StringBuffer sqlbuffSum = new StringBuffer();
                sqlbuffSum.append("select count(distinct defect_code) defect_Cnt from dm.m_defect_gp_rln_d");
                sqlbuffSum.append(" where ope_id = '" + opeid + "'");
                sqlbuffSum.append(" and defect_code_seq is not null");
                sqlbuffSum.append(" and shop_id = '" + shopid + "'");
                sqlbuffSum.append(" group by shop_id,ope_id");
                List<BigInteger> tableColumns = new ArrayList<>();
                try {
                    tableColumns = gpDao.findBySQL(sqlbuffSum.toString());  //获取表中defect_code_seq值不为null的数量
                } catch (Exception e) {
                    logger.error(LoggerUtil.stackTraceToString(e));
                    try {
                        throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
                    } catch (ETLException e1) {
                        e1.printStackTrace();
                    }
                }

                //更新defect_code_seq值
                StringBuffer sqlbuffUpd = new StringBuffer();

                if(tableColumns == null || tableColumns.size() == 0)
                {
                    sqlbuffUpd.append("update dm.m_defect_gp_rln_d set defect_code_seq = 'DEFECTQTY_1'");
                    sqlbuffUpd.append(" where shop_id = '" + shopid + "'");
                    sqlbuffUpd.append(" and ope_id = '" + opeid + "'");
                    sqlbuffUpd.append(" and defect_code = '" + defectcode + "'");
                }
                else
                {
                    tableColumns.forEach(count -> {
                        sqlbuffUpd.append("update dm.m_defect_gp_rln_d set defect_code_seq = 'DEFECTQTY_'|| '" + (Integer.valueOf(count.toString()) + 1) + "'");
                        sqlbuffUpd.append(" where shop_id = '" + shopid + "'");
                        sqlbuffUpd.append(" and ope_id = '" + opeid + "'");
                        sqlbuffUpd.append(" and defect_code = '" + defectcode + "'");
                    });
                }

                try {
                    SQLQuery upd_query = session.createSQLQuery(sqlbuffUpd.toString());
                    upd_query.executeUpdate();
                } catch (Exception e) {
                    logger.error(LoggerUtil.stackTraceToString(e));
                    logger.error("************   sqlbuffUpd.toString():  **********    " + sqlbuffUpd.toString());
                    e.printStackTrace();
                    try {
                        throw new ETLException("ERROR hisAndExtToTmp Update:" + LoggerUtil.stackTraceToString(e));
                    } catch (ETLException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            throw new ETLException("ERROR hisAndExtToTmp GET COLUMN_NAME:" + LoggerUtil.stackTraceToString(e));
        }

    }

}

